﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UniMana.Data;
using System.Data.Linq;
using System.Data;
using UniMana.Utils.Helper;
using UniMana.Utils.Data;

namespace UniMana.Data
{
    public static class Report
    {

        private static IQueryable GetStudentScoreBySchoolYear_Major(
            Table<Training_Subject> subject,
            Table<Training_Subject_Class> subject_Class,
            Table<Training_Student_Subject_Class> student_Subject_Class,
            Table<Training_TrainingProgram> trainingProgram,
            Table<Training_Student> student,
            Table<Training_Score_Transfer> score_Transfer,
            int schoolYearKey = 0,
            int majorKey = 0)
        {
            var Query = from sj in subject
                        join sjc in subject_Class on sj.Training_SubjectKey equals sjc.Training_SubjectKey
                        join st_sjc in student_Subject_Class on sjc.Training_Subject_ClassKey equals st_sjc.Training_Subject_ClassKey
                        join st in student on st_sjc.Training_StudentKey equals st.Training_StudentKey
                        join trp in trainingProgram on st_sjc.Training_TrainingProgramKey equals trp.Training_TrainingProgramKey
                        where
                          sjc.Catalogue_SchoolYearKey == schoolYearKey
                        orderby
                          trp.Catalogue_MajorKey,
                          trp.Catalogue_CourseKey,
                          trp.Catalogue_LevelKey,
                          st_sjc.Training_StudentKey,
                          st.LastName,
                          st.SureName,
                          st.Gender,
                          st.Birthday,
                          st.Email,
                          st.HomePhone,
                          st.CellPhone,
                          st.WorkPhone,
                          sj.Training_SubjectKey,
                          sjc.Training_Subject_ClassKey
                        select new
                        {
                            trp.Catalogue_MajorKey,
                            st_sjc.Training_StudentKey,
                            trp.Catalogue_CourseKey,
                            trp.Catalogue_LevelKey,
                            sj.Training_SubjectKey,
                            sjc.Training_Subject_ClassKey,
                            st_sjc.AverageScore,
                            st_sjc.AverageScoreFinal,
                            sj.TotalCredits,
                            st.LastName,
                            st.SureName,
                            Gender = st.Gender == true ? "Nữ" : "Nam",
                            st.Birthday,
                            st.Email,
                            st.HomePhone,
                            st.CellPhone,
                            st.WorkPhone
                        };
            var Query_ = from st_sjc in student_Subject_Class
                         join sc_tr in score_Transfer on st_sjc.Training_Subject_ClassKey equals sc_tr.Training_Subject_ClassKey
                         join sjc in subject_Class on st_sjc.Training_Subject_ClassKey equals sjc.Training_Subject_ClassKey
                         join sj in subject on sjc.Training_SubjectKey equals sj.Training_SubjectKey
                         join st in student on st_sjc.Training_StudentKey equals st.Training_StudentKey
                         join trp in trainingProgram on sc_tr.Training_TrainingProgramKeyNew equals trp.Training_TrainingProgramKey
                         where
                          st_sjc.Training_StudentKey == sc_tr.Training_StudentKey &&
                          sc_tr.Training_TrainingProgramKey == st_sjc.Training_TrainingProgramKey &&
                           sjc.Catalogue_SchoolYearKey == 1
                         orderby
                           sc_tr.Training_TrainingProgramKeyNew,
                           trp.Catalogue_MajorKey,
                           trp.Catalogue_CourseKey,
                           trp.Catalogue_LevelKey,
                           st.Training_StudentKey,
                           st.LastName,
                           st.SureName,
                           st.Gender,
                           st.Birthday,
                           st.Email,
                           st.HomePhone,
                           st.CellPhone,
                           sjc.Training_Subject_ClassKey,
                           sj.Training_SubjectKey
                         select new
                         {
                             trp.Catalogue_MajorKey,
                             st.Training_StudentKey,
                             trp.Catalogue_CourseKey,
                             trp.Catalogue_LevelKey,
                             sj.Training_SubjectKey,
                             sjc.Training_Subject_ClassKey,
                             st_sjc.AverageScore,
                             st_sjc.AverageScoreFinal,
                             sj.TotalCredits,
                             st.LastName,
                             st.SureName,
                             Gender = st.Gender == true ? "Nữ" : "Nam",
                             st.Birthday,
                             st.Email,
                             st.HomePhone,
                             st.CellPhone,
                             st.WorkPhone
                         };
            if (majorKey > 0)
            {
                Query = Query.Where(q => q.Catalogue_MajorKey == majorKey);
                Query_ = Query_.Where(q => q.Catalogue_MajorKey == majorKey);
            }
            var Q = Query.Union(Query_);
            return Query.Distinct();
        }

        public static DataTable Scoring(
                    Table<Training_Subject> subject,
                    Table<Training_Subject_Class> subject_Class,
                    Table<Training_Student_Subject_Class> student_Subject_Class,
                    Table<Training_TrainingProgram> trainingProgram,
                    Table<Training_Student> student,
                    Table<Training_Score_Transfer> score_Transfer,
                    int schoolYearKey = 0,
                    int majorKey = 0)
        {
            var Student_Score = GetStudentScoreBySchoolYear_Major(
                    subject,
                    subject_Class,
                    student_Subject_Class,
                    trainingProgram,
                    student,
                    score_Transfer,
                    schoolYearKey,
                    majorKey);
            DataTable dt = new DataTable();
            dt = Student_Score.ToDataTable();
            DataTable Data = new DataTable();
            if (dt.Rows.Count > 0)
            {
                Data.Columns.Add("Catalogue_MajorKey");
                Data.Columns.Add("Training_StudentKey");
                Data.Columns.Add("Catalogue_CourseKey");
                Data.Columns.Add("Catalogue_LevelKey");
                Data.Columns.Add("AVG_Score");
                Data.Columns.Add("LastName");
                Data.Columns.Add("SureName");
                Data.Columns.Add("Gender");
                Data.Columns.Add("Birthday");
                Data.Columns.Add("Email");
                Data.Columns.Add("HomePhone");
                Data.Columns.Add("CellPhone");
                Data.Columns.Add("WorkPhone");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int TotalCredit = 0;
                    int TotalScore = 0;
                    for (int j = i; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[i]["Catalogue_MajorKey"].ToString() == dt.Rows[j]["Catalogue_MajorKey"].ToString() &&
                            dt.Rows[i]["Training_StudentKey"].ToString() == dt.Rows[j]["Training_StudentKey"].ToString())
                        {
                            if (dt.Rows[j]["AverageScoreFinal"] != DBNull.Value)
                            {
                                TotalScore += Convert.ToInt16(dt.Rows[j]["AverageScoreFinal"]) * Convert.ToInt16(dt.Rows[j]["TotalCredits"]);
                            }
                            else
                            {
                                if (dt.Rows[j]["AverageScore"] != DBNull.Value)
                                {
                                    TotalScore += Convert.ToInt16(dt.Rows[j]["AverageScore"]) * Convert.ToInt16(dt.Rows[j]["TotalCredits"]);
                                }
                                else
                                {
                                }
                            }
                            if (dt.Rows[j]["TotalCredits"] != DBNull.Value)
                            {
                                TotalCredit += Convert.ToInt16(dt.Rows[j]["TotalCredits"]);
                            }
                        }
                        else
                        {
                            i = j;
                            break;
                        }
                    }
                    Data.Rows.Add();
                    Data.Rows[Data.Rows.Count - 1]["Catalogue_MajorKey"] = dt.Rows[i]["Catalogue_MajorKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Training_StudentKey"] = dt.Rows[i]["Training_StudentKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Catalogue_CourseKey"] = dt.Rows[i]["Catalogue_CourseKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Catalogue_LevelKey"] = dt.Rows[i]["Catalogue_LevelKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["AVG_Score"] = Math.Round(TotalScore / (double)TotalCredit, 4);
                    Data.Rows[Data.Rows.Count - 1]["LastName"] = dt.Rows[i]["LastName"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["SureName"] = dt.Rows[i]["SureName"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Gender"] = dt.Rows[i]["Gender"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Birthday"] = dt.Rows[i]["Birthday"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Email"] = dt.Rows[i]["Email"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["HomePhone"] = dt.Rows[i]["HomePhone"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["CellPhone"] = dt.Rows[i]["CellPhone"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["WorkPhone"] = dt.Rows[i]["WorkPhone"].ToString();
                }
            }
            else
            {
                Data.Columns.Add();
                Data.Rows.Add();
                Data.Rows[0][0] = "@000@";
            }
            return Data;
        }
    }
}
